import psycopg2

import sys
import os
# 获取当前脚本所在目录
current_dir = os.path.dirname(os.path.abspath(__file__))
# 获取项目根目录
project_root = os.path.abspath(os.path.join(current_dir, '..'))
# 将项目根目录、workhours 目录和 init 目录添加到 sys.path
sys.path.append(project_root)
sys.path.append(os.path.join(project_root, 'workhours'))
sys.path.append(os.path.join(project_root, 'init'))
from ..config.db_params import db_params


def handle_t_project_info(conn, cursor):
    # 检查表是否存在
    v_check_table_query = "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 't_project_info')"
    cursor.execute(v_check_table_query)
    v_table_exists = cursor.fetchone()[0]

    if not v_table_exists:
        # 创建表
        v_create_table_query = '''
        CREATE TABLE t_project_info (
            project_id SERIAL PRIMARY KEY,
            project_code VARCHAR(50) NOT NULL,    -- 项目编号
            project_name TEXT NOT NULL,           -- 项目名称
            organization TEXT,                    -- 归属公司
            department TEXT,                      -- 项目归属部门
            project_status TEXT,                  -- 项目状态
            project_manager TEXT,                 -- 项目负责人
            project_manager_id VARCHAR(50),       -- 项目负责人编号
            project_type TEXT,                    -- 项目类型
            pre_approval_date DATE,               -- 预立项时间
            signing_date DATE,                    -- 签约时间
            signing_status TEXT,                  -- 签约状态
            signing_date2 DATE,                   -- 签约时间2
            creation_time TIMESTAMP,              -- 创建时间
            approval_date DATE,                   -- 立项时间
            termination_date DATE,                -- 项目终止时间
            pre_investment_limit NUMERIC(15, 2),  -- 预投入限额
            pre_investment_total NUMERIC(15, 2),  -- 预投入总额
            project_source TEXT,                  -- 项目来源
            parent_project_code VARCHAR(50),      -- 关联主项目编号
            is_main_project TEXT,                 -- 是否是主项目
            expected_sales_amount NUMERIC(15, 2), -- 预计销售金额
            customer_name TEXT                    -- 客户名称
        );

        -- 单列索引
        CREATE INDEX idx_t_project_project_code ON t_project_info (project_code);
        CREATE INDEX idx_t_project_project_name ON t_project_info (project_name);
        CREATE INDEX idx_t_project_organization ON t_project_info (organization);
        CREATE INDEX idx_t_project_project_manager_id ON t_project_info (project_manager_id);
        CREATE INDEX idx_t_project_project_status ON t_project_info (project_status);
        CREATE INDEX idx_t_project_project_type ON t_project_info (project_type);
        CREATE INDEX idx_t_project_project_source ON t_project_info (project_source);
        CREATE INDEX idx_t_project_pre_approval_date ON t_project_info (pre_approval_date);
        CREATE INDEX idx_t_project_approval_date ON t_project_info (approval_date);
        CREATE INDEX idx_t_project_creation_time ON t_project_info (creation_time);

        -- 添加字段注释
        COMMENT ON COLUMN t_project_info.project_code IS '项目编号';
        COMMENT ON COLUMN t_project_info.project_name IS '项目名称';
        COMMENT ON COLUMN t_project_info.organization IS '归属公司';
        COMMENT ON COLUMN t_project_info.project_manager IS '项目负责人';
        COMMENT ON COLUMN t_project_info.project_status IS '项目状态';
        COMMENT ON COLUMN t_project_info.project_type IS '项目类型';
        COMMENT ON COLUMN t_project_info.project_source IS '项目来源';
        COMMENT ON COLUMN t_project_info.pre_approval_date IS '预立项时间';
        COMMENT ON COLUMN t_project_info.approval_date IS '立项时间';
        COMMENT ON COLUMN t_project_info.creation_time IS '创建时间';
        '''
        cursor.execute(v_create_table_query)
        print('表 t_project_info 创建成功')

    if v_table_exists:
        # 清理表中全部数据
        v_delete_query = "TRUNCATE TABLE t_project_info RESTART IDENTITY"
        cursor.execute(v_delete_query)
        conn.commit()
        print('表 t_project_info 数据清理成功')


def handle_t_employee_info(conn, cursor):
    # 检查表是否存在
    v_check_table_query = "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 't_employee_info')"
    cursor.execute(v_check_table_query)
    v_table_exists = cursor.fetchone()[0]

    if not v_table_exists:
        # 创建表
        v_create_table_query = '''
        CREATE TABLE t_employee_info (
            employ_id SERIAL PRIMARY KEY,
            employee_code VARCHAR(50) NOT NULL,    -- 人员编号
            employee_name TEXT NOT NULL,           -- 人员姓名
            organization TEXT,                     -- 人员所属公司
            department TEXT,                       -- 人员所属部门
            employee_status TEXT,                  -- 人员状态
            job_position TEXT,                     -- 人员岗位
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 记录创建时间
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP   -- 记录更新时间
        );

        -- 索引（命名规则：idx_简化表名_字段缩写）
        CREATE INDEX idx_emp_code ON t_employee_info (employee_code);       -- 人员编号
        CREATE INDEX idx_emp_name ON t_employee_info (employee_name);       -- 人员姓名
        CREATE INDEX idx_emp_cmp ON t_employee_info (organization);         -- 人员所属公司
        CREATE INDEX idx_emp_dept ON t_employee_info (department);          -- 人员所属部门
        CREATE INDEX idx_emp_sts ON t_employee_info (employee_status);      -- 人员状态
        CREATE INDEX idx_emp_pos ON t_employee_info (job_position);         -- 人员岗位
        '''
        cursor.execute(v_create_table_query)
        print('表 t_employee_info 创建成功')

    if v_table_exists:
        # 清理表中全部数据
        v_delete_query = "TRUNCATE TABLE t_employee_info RESTART IDENTITY"
        cursor.execute(v_delete_query)
        conn.commit()
        print('表 t_employee_info 数据清理成功')


def handle_t_work_hour_records(conn, cursor):
    # 检查表是否存在
    v_check_table_query = "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 't_work_hour_records')"
    cursor.execute(v_check_table_query)
    v_table_exists = cursor.fetchone()[0]

    if not v_table_exists:
        # 创建表
        v_create_table_query = '''
        CREATE  TABLE  t_work_hour_records (
             record_id SERIAL PRIMARY  KEY,                 -- 自增主键
             organization TEXT,                            -- 所属组织
             organization_code VARCHAR (50),                -- 所属组织编号
             employee_name TEXT,                           -- 姓名
             employee_id VARCHAR (50),                      -- 人员编号
             department TEXT,                              -- 部门
             task_name TEXT,                               -- 任务名称
             project_manager TEXT,                         -- 归属项目经理
             project_name TEXT,                            -- 归属项目
             project_code VARCHAR (50),                     -- 归属项目编号
             task_type TEXT,                               -- 任务类型
             time_report_time TIMESTAMP,                   -- 工时填报时间
             time_belong_date DATE,                        -- 工时归属日期
             reported_hours NUMERIC (6,2),                  -- 对应填报工时（小时）
             approved_hours NUMERIC (6,2),                  -- 实际认定工时（小时）
             approval_time TIMESTAMP,                      -- 认定时间
             other_notes TEXT,                             -- 其他说明
             processing_notes TEXT,                        -- 处理说明
             from_source TEXT,                             -- 来源
             created_at TIMESTAMP  DEFAULT  CURRENT_TIMESTAMP -- 记录创建时间
         );
         -- 添加常用查询字段的索引
         CREATE  INDEX  idx_employee_id ON  t_work_hour_records (employee_id);
         CREATE  INDEX  idx_project_code ON  t_work_hour_records (project_code);
         CREATE  INDEX  idx_time_belong_date ON  t_work_hour_records (time_belong_date);
        '''
        cursor.execute(v_create_table_query)
        conn.commit()
        print('表 t_work_hour_records 创建成功')

    if v_table_exists:
        # 清理表中全部数据
        v_delete_query = "TRUNCATE TABLE t_work_hour_records RESTART IDENTITY"
        cursor.execute(v_delete_query)
        print('表 t_work_hour_records 数据清理成功')


def setup_database(conn, cursor):
    try:
        handle_t_project_info(conn, cursor)
        handle_t_employee_info(conn, cursor)
        handle_t_work_hour_records(conn, cursor)
    except Exception as e:
        print(f'发生错误: {e}')
        raise e  # 重新抛出异常，以便上层调用者可以捕获并处理